import logging
from peewee import MySQLDatabase, Model, CharField, DateField, BooleanField, IntegerField
import csv
#读取数据库与csv的字段是否匹配

csvFileName= "CT缺陷库_0.04(1).csv"

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

db = MySQLDatabase('misas', host='172.31.102.207', user='root', passwd='666.MrJg,175', charset='utf8', port=3306)

class BaseModel(Model):
    class Meta:
        database = db

class Ct_Misas_Second_Grade_Rule(BaseModel):
    # 主键自增
    # ct_rule_id = CharField(verbose_name='邮件', max_length=45, null=True) #主键
    defect_ename = CharField(verbose_name='其他', null=True, default=None)
    defect_cname = CharField(verbose_name='姓名', max_length=23, null=False, index=True)
    language = IntegerField(verbose_name='其他', null=True, default=None)
    defect_severity = IntegerField(verbose_name='生日', null=True, default=None)
    defect_type = IntegerField(verbose_name='其他', null=True, default=None)
    codepecker_defect_ename = CharField(verbose_name='密码', max_length=45, null=False)
    fortify_defect_ename = CharField(verbose_name='其他', null=True, default=None)
    parent_id = IntegerField(verbose_name='其他', null=True, default=None)

class Ct_Misas_First_Grade_Rule(BaseModel):
    # 主键应该是自增的
    # id =  IntegerField(verbose_name='主键', null=True, default=None)
    defect_cname = CharField(verbose_name='一级缺陷中文名', max_length=23, null=False, index=True)
    defect_ename = CharField(verbose_name='一级缺陷英文名', null=True, default=None)
    defect_type = IntegerField(verbose_name='缺陷类型', null=True, default=0)




if __name__ == '__main__':

    with open(csvFileName,encoding='utf-8') as csvfile:
        reader=csv.reader(csvfile)
        csvFirstClassName = None
        csvLanguage=None
        firstId = -1;
        j = 1
        for i, row in enumerate(reader):
            if i == 0:
                print(len(row))
            else:

                # 第一列如果存在就全部是一级缺陷名称
                if row[j] is not None and row[j+0] != '' :
                    csvFirstClassName =row[j+0]
                    # 存储到一级缺陷表中,并且返回自增主键id
                    # 下面返回的是Model
                    result = Ct_Misas_First_Grade_Rule.create(defect_cname=csvFirstClassName)
                    print(result)
                    firstId = result.get_id()
                    print(firstId)
                    continue
                    # continue 并不是所有的一级缺陷名后面都是空的
                # if row[j+1] is not None and row[j+1] != '':
                #     csvName=row[1]
                # else:
                #     continue
                secondCName = row[j+1]
                secondEName = row[j+6]
                codepeckerDefectEname = row[j+3]
                fortifyDefectEname = row[j+5]
                defectType = row[j+7]
                defectSeverity=row[j+8]


                lang = row[j+9]
                if lang.lower() =='java':
                    csvLanguage=1
                elif lang.lower() =='c':
                    csvLanguage=2
                elif lang.lower() =='php':
                    csvLanguage=3
                elif lang.lower() =='python':
                    csvLanguage=4

                try:
                    Ct_Misas_Second_Grade_Rule.create(defect_cname=secondCName,defect_ename=secondEName,codepecker_defect_ename = codepeckerDefectEname,fortify_defect_ename = fortifyDefectEname,defect_type = defectType,defect_severity=defectSeverity,language=csvLanguage,parent_id=firstId,force_insert=False)
                except Exception as e:
                    logger.error(e)
                    pass

    pass

# SELECT Count(*) FROM misas.ct_rule;